﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;

using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using WFL.Data;

namespace WFL.Data.Oracle
{
    public class OracleORM
    {
        public static EntityDefinitionManager EntityDefinitionManager
        {
            get;
            private set;
        }

        static OracleORM()
        {
            EntityDefinitionManager = new EntityDefinitionManager("Oracle");
        }


        private OracleOperation _operation;

        public OracleORM()
        {

        }

        public OracleORM(OracleOperation operation)
        {
            _operation = operation;
        }

        public OracleORM(string connectionString)
        {
            _operation = new OracleOperationWithContext(connectionString);
        }


        #region 私有方法

        private OracleParameter GenerateParameter(PropertyDefinition property, object value)
        {
            if (value is Enum)
            {
                //枚举值默认按照整形处理
                value = Convert.ToInt32(value);
            }
            else if (value is bool)
            {
                //bool数据类型特殊处理
                //规定：0或者"0"表示false，1或者"1"表示true
                switch (property.Field.DbType)
                {
                    default:
                        throw new Exception("无效的bool类型映射");
                    case 0://默认按Char类型处理
                    case (int)OracleDbType.Char:
                        value = ((bool)value) ? "1" : "0";
                        break;
                    case (int)OracleDbType.Int32:
                        value = ((bool)value) ? 1 : 0;
                        break;
                }
            }
            OracleParameter parameter = null;
            if (property.Field.DbType == 0)
            {
                parameter = new OracleParameter(GenerateParameterName(property), value != null ? value : DBNull.Value);
            }
            else
            {
                parameter = new OracleParameter(GenerateParameterName(property), (OracleDbType)property.Field.DbType);
                parameter.Value = (value != null ? value : DBNull.Value);
            }
            return parameter;
        }

        private string GenerateParameterName(PropertyDefinition property)
        {
            if (property.Field.Name[0] == '"')
            {
                return property.PropertyInfo.Name + "X";
            }
            else
            {
                return property.PropertyInfo.Name;
            }
        }

        private void GenerateFromClause<T>(string[] tables, StringBuilder sqlBd)
        {
            EntityDefinition entity = EntityDefinitionManager.Get<T>();
            if (entity.Table != null && tables != null && tables.Length != 1
                || entity.Table == null && tables != null && tables.Length != entity.Combines.Length)
            {
                throw new ArgumentException(string.Format("提供的表格数目：{0}与期望的表格数目：{1}不一致"
                    , entity.Table != null ? "1" : tables.Length.ToString()));
            }
            if (entity.Table != null)
            {
                //非组合类，映射到单表格
                if (tables == null)
                {
                    sqlBd.Append(entity.Table.Name);
                }
                else
                {
                    sqlBd.Append(tables[0]);
                }
                if (!string.IsNullOrEmpty(entity.Table.Alias))
                {
                    sqlBd.Append(" ");
                    sqlBd.Append(entity.Table.Alias);
                }
            }
            else
            {
                //组合类，映射到多表格
                //组合类只会由其他非组合类组合而来
                for (int i = 0; i < entity.Combines.Length; i++)
                {
                    EntityDefinition currEntity = EntityDefinitionManager.Get(entity.Combines[i].Type);
                    if (i > 0)
                    {
                        if (currEntity.ForeignKeyProperties == null)
                        {
                            throw new Exception(string.Format("类型\"{0}\"缺少外键", currEntity.Type.FullName));
                        }
                        sqlBd.Append(" join ");
                    }
                    if (tables != null)
                    {
                        sqlBd.Append(tables[i]);
                    }
                    else
                    {
                        sqlBd.Append(currEntity.Table.Name);
                    }
                    if (!string.IsNullOrEmpty(currEntity.Table.Alias))
                    {
                        sqlBd.Append(currEntity.Table.Alias);
                    }
                    if (i > 0)
                    {
                        //创建连接条件
                        sqlBd.Append(" on ");
                        EntityDefinition lastEntity = EntityDefinitionManager.Get(entity.Combines[i - 1].Type);
                        foreach (PropertyDefinition foreignKeyProperty in currEntity.ForeignKeyProperties)
                        {
                            //判断当前类型是否外键关联到上一个类型
                            if (foreignKeyProperty.ForeignKey.ReferenceType == lastEntity.Type)
                            {
                                if (!string.IsNullOrEmpty(lastEntity.Table.Alias))
                                {
                                    sqlBd.Append(lastEntity.Table.Alias);
                                    sqlBd.Append(".");
                                }
                                sqlBd.Append(lastEntity.GetProperty(foreignKeyProperty.ForeignKey.ReferencePropertyName).Field.Name);
                                sqlBd.Append("=");
                                if (!string.IsNullOrEmpty(currEntity.Table.Alias))
                                {
                                    sqlBd.Append(currEntity.Table.Alias);
                                    sqlBd.Append(".");
                                }
                                sqlBd.Append(foreignKeyProperty.Field.Name);
                                //当前不支持联合主键，所以此处可以跳出
                                break;
                            }
                        }
                    }
                }
                throw new NotImplementedException();
            }
        }

        #endregion


        #region Insert

        private void GenerateInsertSql<T>(T item, string table, string[] properties, out string sql, out OracleParameter[] parameters)
        {
            EntityDefinition entity = EntityDefinitionManager.Get<T>();
            if (string.IsNullOrEmpty(table))
            {
                table = entity.Table.Name;
            }
            StringBuilder sqlBd = new StringBuilder();
            if (entity.PrimaryKeyProperty != null && entity.PrimaryKeyProperty.PrimaryKey.Automatic)
            {
                sqlBd.Append("begin select ");
                //处理表明包含引号的问题
                sqlBd.Append(table.StartsWith("\"") ? table.Substring(1, table.Length - 2) : table);
                sqlBd.Append("_id.nextval into :");
                sqlBd.Append(GenerateParameterName(entity.PrimaryKeyProperty));
                sqlBd.Append(" from dual;");
            }
            sqlBd.Append("insert into ");
            sqlBd.Append(table);
            sqlBd.Append("(");
            if (properties == null || properties.Length == 0)
            {
                foreach (PropertyDefinition property in entity.Properties)
                {
                    if ((property.Persist.Behaviors & PersistBehaviors.Insert) != PersistBehaviors.Insert)
                    {
                        continue;
                    }
                    sqlBd.Append(property.Field.Name);
                    sqlBd.Append(',');
                }
            }
            else
            {
                //指示Id是否包含在列表中
                bool idContained = false;
                foreach (string propertyName in properties)
                {
                    PropertyDefinition property = entity.GetProperty(propertyName);
                    sqlBd.Append(property.Field.Name);
                    sqlBd.Append(',');
                    if (property.PrimaryKey != null)
                    {
                        idContained = true;
                    }
                }
                if (!idContained
                    && entity.PrimaryKeyProperty != null
                    && (entity.PrimaryKeyProperty.Persist.Behaviors & PersistBehaviors.Insert) == PersistBehaviors.Insert)
                {
                    sqlBd.Append(entity.PrimaryKeyProperty.Field.Name);
                    sqlBd.Append(',');
                }
            }
            sqlBd.Remove(sqlBd.Length - 1, 1);
            sqlBd.Append(")values(");
            List<OracleParameter> parameterList = new List<OracleParameter>(8);
            if (properties == null || properties.Length == 0)
            {
                foreach (PropertyDefinition property in entity.Properties)
                {
                    if ((property.Persist.Behaviors & PersistBehaviors.Insert) != PersistBehaviors.Insert)
                    {
                        continue;
                    }
                    sqlBd.Append(':');
                    sqlBd.Append(GenerateParameterName(property));
                    sqlBd.Append(',');
                    object value = property.PropertyInfo.GetValue(item, null);
                    OracleParameter parameter = GenerateParameter(property, value);
                    parameterList.Add(parameter);
                    if (property.PrimaryKey != null && property.PrimaryKey.Automatic)
                    {
                        parameterList[parameterList.Count - 1].Direction = ParameterDirection.Output;
                    }
                }
            }
            else
            {
                bool idContained = false;
                foreach (string propertyName in properties)
                {
                    PropertyDefinition property = entity.GetProperty(propertyName);
                    sqlBd.Append(':');
                    sqlBd.Append(GenerateParameterName(property));
                    sqlBd.Append(',');
                    object value = property.PropertyInfo.GetValue(item, null);
                    OracleParameter parameter = GenerateParameter(property, value);
                    parameterList.Add(parameter);
                    if (property.PrimaryKey != null)
                    {
                        idContained = true;
                        if (property.PrimaryKey.Automatic)
                        {
                            parameterList[parameterList.Count - 1].Direction = ParameterDirection.Output;
                        }
                    }
                }
                if (!idContained
                    && entity.PrimaryKeyProperty != null
                    && (entity.PrimaryKeyProperty.Persist.Behaviors & PersistBehaviors.Insert) == PersistBehaviors.Insert)
                {
                    sqlBd.Append(':');
                    sqlBd.Append(GenerateParameterName(entity.PrimaryKeyProperty));
                    sqlBd.Append(',');
                    object value = entity.PrimaryKeyProperty.PropertyInfo.GetValue(item, null);
                    OracleParameter parameter = GenerateParameter(entity.PrimaryKeyProperty, value);
                    if (entity.PrimaryKeyProperty.PrimaryKey.Automatic)
                    {
                        parameter.Direction = ParameterDirection.Output;
                    }
                    parameterList.Add(parameter);
                }
            }
            sqlBd.Remove(sqlBd.Length - 1, 1);
            sqlBd.Append(")");
            if (entity.PrimaryKeyProperty != null && entity.PrimaryKeyProperty.PrimaryKey.Automatic)
            {
                sqlBd.Append(";end;");
            }
            sql = sqlBd.ToString();
            parameters = parameterList.ToArray();
        }

        public int Insert<T>(T item)
        {
            return Insert<T>(item, null, (string[])null);
        }

        public int Insert<T>(T item, string table, string[] properties)
        {
            string sql = null;
            OracleParameter[] parameters = null;
            GenerateInsertSql<T>(item, table, properties, out sql, out parameters);
            int result = _operation.ExecuteNonQuery(sql, parameters);
            EntityDefinition entity = EntityDefinitionManager.Get<T>();
            if (entity.PrimaryKeyProperty != null && entity.PrimaryKeyProperty.PrimaryKey.Automatic)
            {
                string idParamName = GenerateParameterName(entity.PrimaryKeyProperty);
                OracleParameter idParam = null;
                foreach (OracleParameter parameter in parameters)
                {
                    if (parameter.ParameterName == idParamName)
                    {
                        idParam = parameter;
                        break;
                    }
                }
                if (idParam.Value is IConvertible)
                {
                    entity.PrimaryKeyProperty.PropertyInfo.SetValue(item, Convert.ChangeType(idParam.Value, entity.PrimaryKeyProperty.PropertyInfo.PropertyType), null);
                }
                else if (idParam.Value is OracleDecimal)
                {
                    entity.PrimaryKeyProperty.PropertyInfo.SetValue(item, Convert.ChangeType(((OracleDecimal)(idParam.Value)).Value, entity.PrimaryKeyProperty.PropertyInfo.PropertyType), null);
                }
                else
                {
                    entity.PrimaryKeyProperty.PropertyInfo.SetValue(item, idParam.Value, null);
                }
            }
            return result;
        }

        #endregion


        #region Update

        private void GenerateUpdateSql<T>(T item, string table, string[] properties, string condition, OracleParameter[] conditionParameters, out string sql, out OracleParameter[] parameters)
        {
            EntityDefinition entity = EntityDefinitionManager.Get<T>();
            if (string.IsNullOrEmpty(table))
            {
                table = entity.Table.Name;
            }
            StringBuilder sqlBd = new StringBuilder();
            sqlBd.Append("update ");
            sqlBd.Append(table);
            sqlBd.Append(" set ");
            List<OracleParameter> parameterList = new List<OracleParameter>();
            if (properties == null || properties.Length == 0)
            {
                foreach (PropertyDefinition property in entity.Properties)
                {
                    if (property.PrimaryKey != null
                        || (property.Persist.Behaviors & PersistBehaviors.Update) != PersistBehaviors.Update)
                    {
                        continue;
                    }
                    sqlBd.Append(property.Field.Name);
                    sqlBd.Append("=:");
                    sqlBd.Append(GenerateParameterName(property));
                    sqlBd.Append(',');
                    object value = property.PropertyInfo.GetValue(item, null);
                    OracleParameter parameter = GenerateParameter(property, value);
                    parameterList.Add(parameter);
                }
            }
            else
            {
                foreach (string propertyName in properties)
                {
                    PropertyDefinition property = entity.GetProperty(propertyName);
                    sqlBd.Append(property.Field.Name);
                    sqlBd.Append("=:");
                    sqlBd.Append(GenerateParameterName(property));
                    sqlBd.Append(',');
                    object value = property.PropertyInfo.GetValue(item, null);
                    OracleParameter parameter = GenerateParameter(property, value);
                    parameterList.Add(parameter);
                }
            }
            //移除末尾的逗号
            sqlBd.Remove(sqlBd.Length - 1, 1);
            //更新满足指定条件的行
            if (!string.IsNullOrEmpty(condition))
            {
                sqlBd.Append(" where ");
                sqlBd.Append(condition);
            }
            sql = sqlBd.ToString();
            if (conditionParameters != null)
            {
                parameterList.AddRange(conditionParameters);
            }
            parameters = parameterList.ToArray();
        }

        public int Update<T>(T item)
        {
            return Update<T>(item, null, (string[])null);
        }

        public int Update<T>(T item, string table, string[] properties)
        {
            EntityDefinition entity = EntityDefinitionManager.Get<T>();
            if (entity.PrimaryKeyProperty == null)
            {
                throw new Exception(string.Format("类型{0}未声明主键", entity.Type.Name));
            }
            string condition = string.Format("{0}=:{1}", entity.PrimaryKeyProperty.Field.Name, entity.PrimaryKeyProperty.PropertyInfo.Name);
            OracleParameter[] conditionParameters = new OracleParameter[]
            {
                GenerateParameter(entity.PrimaryKeyProperty,entity.PrimaryKeyProperty.PropertyInfo.GetValue(item,null))
            };
            return Update<T>(item, table, properties, condition, conditionParameters);
        }

        public int Update<T>(T item, string table, string[] properties, string condition, OracleParameter[] conditionParameters)
        {
            string sql = null;
            OracleParameter[] parameters = null;
            GenerateUpdateSql<T>(item, table, properties, condition, conditionParameters, out sql, out parameters);
            return _operation.ExecuteNonQuery(sql, parameters);
        }

        #endregion


        #region Delete

        private void GenerateDeleteSql<T>(string table, string condition, OracleParameter[] conditionParameters, out string sql, out OracleParameter[] parameters)
        {
            EntityDefinition entity = EntityDefinitionManager.Get<T>();
            StringBuilder sqlBd = new StringBuilder();
            sqlBd.Append("delete from ");
            if (string.IsNullOrEmpty(table))
            {
                sqlBd.Append(entity.Table.Name);
            }
            else
            {
                sqlBd.Append(table);
            }
            parameters = null;
            if (!string.IsNullOrEmpty(condition))
            {
                sqlBd.Append(" where ");
                sqlBd.Append(condition);
                parameters = conditionParameters;
            }
            sql = sqlBd.ToString();
        }

        public int Delete<T>(object id)
        {
            return Delete<T>(id, null);
        }

        public int Delete<T>(object id, string table)
        {
            EntityDefinition entity = EntityDefinitionManager.Get<T>();
            if (entity.PrimaryKeyProperty == null)
            {
                throw new Exception(string.Format("类型{0}未声明主键", entity.Type.Name));
            }
            string condition = string.Format("{0}=:{1}", entity.PrimaryKeyProperty.Field.Name, entity.PrimaryKeyProperty.PropertyInfo.Name);
            OracleParameter[] conditionParameters = new OracleParameter[]
            {
                GenerateParameter(entity.PrimaryKeyProperty,id)
            };
            return Delete<T>(table, condition, conditionParameters);
        }

        public int Delete<T>(string condition, OracleParameter[] conditionParameters)
        {
            return Delete<T>(null, condition, conditionParameters);
        }

        public int Delete<T>(string table, string condition, OracleParameter[] conditionParameters)
        {
            string sql = null;
            OracleParameter[] parameters = null;
            GenerateDeleteSql<T>(table, condition, conditionParameters, out sql, out parameters);
            return _operation.ExecuteNonQuery(sql, parameters);
        }

        #endregion


        #region Select

        public T Select<T>(object id) where T : new()
        {
            return Select<T>(id, (string[])null, null);
        }

        public T Select<T>(object id, string[] tables, string[] properties) where T : new()
        {
            EntityDefinition entity = EntityDefinitionManager.Get<T>();
            if (entity.PrimaryKeyProperty == null)
            {
                throw new Exception(string.Format("类型{0}未声明主键", entity.Type.Name));
            }
            string condition = string.Format("{0}=:{1}", entity.PrimaryKeyProperty.Field.Name, entity.PrimaryKeyProperty.PropertyInfo.Name);
            OracleParameter[] conditionParameters = new OracleParameter[]
            {
                GenerateParameter(entity.PrimaryKeyProperty,id)
            };
            IList<T> list = SelectList<T>(tables, properties, condition, conditionParameters, null);
            return list == null ? default(T) : list[0];
        }

        #endregion


        #region SelectList

        private void GenerateSelectListSql<T>(string[] tables, string[] properties, string condition, OracleParameter[] conditionParameters, string order, out string sql, out OracleParameter[] parameters)
        {
            EntityDefinition entity = EntityDefinitionManager.Get<T>();
            StringBuilder sqlBd = new StringBuilder();
            sqlBd.Append("select ");
            if (properties == null || properties.Length == 0)
            {
                foreach (PropertyDefinition property in entity.Properties)
                {
                    if ((property.Persist.Behaviors & PersistBehaviors.Select) != PersistBehaviors.Select)
                    {
                        continue;
                    }
                    sqlBd.Append(property.Field.Name);
                    sqlBd.Append(',');
                }
            }
            else
            {
                foreach (string propertyName in properties)
                {
                    PropertyDefinition property = entity.GetProperty(propertyName);
                    sqlBd.Append(property.Field.Name);
                    sqlBd.Append(',');
                }
            }
            //移除末尾的逗号
            sqlBd.Remove(sqlBd.Length - 1, 1);
            sqlBd.Append(" from ");
            GenerateFromClause<T>(tables, sqlBd);
            parameters = null;
            if (!string.IsNullOrEmpty(condition))
            {
                sqlBd.Append(" where ");
                sqlBd.Append(condition);
                parameters = conditionParameters;
            }
            if (!string.IsNullOrEmpty(order))
            {
                sqlBd.Append(" order by ");
                sqlBd.Append(order);
            }
            sql = sqlBd.ToString();
        }

        private IList<T> GetEntitiesFromReader<T>(string[] properties, OracleDataReader reader) where T : new()
        {
            if (reader.Read())
            {
                EntityDefinition entity = EntityDefinitionManager.Get<T>();
                List<T> list = new List<T>();
                do
                {
                    T item = new T();
                    if (properties == null || properties.Length == 0)
                    {
                        int i = 0;
                        foreach (PropertyDefinition property in entity.Properties)
                        {
                            if ((property.Persist.Behaviors & PersistBehaviors.Select) != PersistBehaviors.Select)
                            {
                                continue;
                            }
                            object value = reader[i++];
                            if (!(value is DBNull))
                            {
                                if (value.GetType() != property.PropertyInfo.PropertyType)
                                {
                                    if (property.PropertyInfo.PropertyType.IsPrimitive)
                                    {
                                        if (property.PropertyInfo.PropertyType == typeof(bool))
                                        {
                                            //bool数据类型特殊处理
                                            //规定：0或者"0"表示false，1或者"1"表示true
                                            switch (property.Field.DbType)
                                            {
                                                default:
                                                    throw new Exception("无效的bool类型映射");
                                                case 0://默认按Char类型处理
                                                case (int)OracleDbType.Char:
                                                    value = value.Equals("1");
                                                    break;
                                                case (int)OracleDbType.Int32:
                                                    value = value.Equals(1);
                                                    break;
                                            }
                                        }
                                        else
                                        {
                                            value = Convert.ChangeType(value, property.PropertyInfo.PropertyType);
                                        }
                                    }
                                    else if (property.PropertyInfo.PropertyType.IsEnum)
                                    {
                                        value = Convert.ChangeType(value, Enum.GetUnderlyingType(property.PropertyInfo.PropertyType));
                                    }
                                    else if (property.PropertyInfo.PropertyType.IsGenericType)
                                    {
                                        if (property.PropertyInfo.PropertyType.Name == "Nullable`1")
                                        {
                                            value = Activator.CreateInstance(property.PropertyInfo.PropertyType, Convert.ChangeType(value, Nullable.GetUnderlyingType(property.PropertyInfo.PropertyType)));
                                        }
                                    }
                                }
                                property.PropertyInfo.SetValue(item, value, null);
                            }
                        }
                    }
                    else
                    {
                        for (int i = 0; i < properties.Length; i++)
                        {
                            PropertyDefinition property = entity.GetProperty(properties[i]);
                            object value = reader[i];
                            if (!(value is DBNull))
                            {
                                if (value.GetType() != property.PropertyInfo.PropertyType)
                                {
                                    if (property.PropertyInfo.PropertyType.IsPrimitive)
                                    {
                                        value = Convert.ChangeType(value, property.PropertyInfo.PropertyType);
                                    }
                                }
                                property.PropertyInfo.SetValue(item, value, null);
                            }
                        }
                    }
                    list.Add(item);
                } while (reader.Read());
                return list;
            }
            return null;
        }

        public IList<T> SelectList<T>(string condition, OracleParameter[] conditionParameters, string order) where T : new()
        {
            return SelectList<T>(null, null, condition, conditionParameters, order);
        }

        public IList<T> SelectList<T>(string[] tables, string[] properties, string condition, OracleParameter[] conditionParameters, string order) where T : new()
        {
            string sql = null;
            OracleParameter[] parameters = null;
            GenerateSelectListSql<T>(tables, properties, condition, conditionParameters, order, out sql, out parameters);
            OracleDataReaderExtension reader = _operation.ExecuteReaderExtension(sql, parameters);
            try
            {
                return GetEntitiesFromReader<T>(properties, reader.Reader);
            }
            finally
            {
                reader.Close();
            }
        }

        public IList<T> SelectList<T>(string sql, OracleParameter[] parameters) where T : new()
        {
            OracleDataReaderExtension reader = _operation.ExecuteReaderExtension(sql, parameters);
            try
            {
                return GetEntitiesFromReader<T>(null, reader.Reader);
            }
            finally
            {
                reader.Close();
            }
        }

        #endregion


        #region SelectListByPage

        private void GenerateSelectListByPageSql<T>(string[] tables, string[] properties, string condition, OracleParameter[] conditionParameters, string order, int pageIndex, int pageSize, out string sql, out OracleParameter[] parameters)
        {
            EntityDefinition entity = EntityDefinitionManager.Get<T>();
            StringBuilder sqlBd = new StringBuilder();
            /*
             select {0} from
             (select rownum rn__,tpt1.* from (select {0} from {1}{2}{3}) tpt1 where rownum<={4})
             where rn__>{5}
             */
            sqlBd.Append("select ");
            StringBuilder insideFields = new StringBuilder();
            if (properties == null || properties.Length == 0)
            {
                for (int i = 0; i < entity.Properties.Length; i++)
                {
                    PropertyDefinition property = entity.Properties[i];
                    if ((property.Persist.Behaviors & PersistBehaviors.Select) != PersistBehaviors.Select)
                    {
                        continue;
                    }
                    sqlBd.Append(string.Format("fd{0},", i.ToString()));
                    //取别名，同名的字段会冲突
                    insideFields.Append(string.Format("{0} fd{1},"
                        , property.Field.Name
                        , i.ToString()));
                }
            }
            else
            {
                for (int i = 0; i < properties.Length; i++)
                {
                    PropertyDefinition property = entity.GetProperty(properties[i]);
                    sqlBd.Append(string.Format("fd{0},", i.ToString()));
                    //取别名，同名的字段会冲突
                    insideFields.Append(string.Format("{0} fd{1},"
                       , property.Field.Name
                       , i.ToString()));
                }
            }
            sqlBd.Remove(sqlBd.Length - 1, 1);
            insideFields.Remove(insideFields.Length - 1, 1);
            sqlBd.Append(" from (select rownum rn__,tpt1.* from (select ");
            sqlBd.Append(insideFields.ToString());
            sqlBd.Append(" from ");
            GenerateFromClause<T>(tables, sqlBd);
            parameters = null;
            if (!string.IsNullOrEmpty(condition))
            {
                sqlBd.Append(" where ");
                sqlBd.Append(condition);
                parameters = conditionParameters;
            }
            if (!string.IsNullOrEmpty(order))
            {
                sqlBd.Append(" order by ");
                sqlBd.Append(order);
            }
            sqlBd.Append(") tpt1 where rownum<=");
            sqlBd.Append(((pageIndex + 1) * pageSize).ToString());
            sqlBd.Append(") tbt2 where rn__>");
            sqlBd.Append((pageIndex * pageSize).ToString());
            sql = sqlBd.ToString();
        }

        public IList<T> SelectListByPage<T>(string condition, OracleParameter[] conditionParameters, string order, int pageIndex, int pageSize) where T : new()
        {
            return SelectListByPage<T>(null, null, condition, conditionParameters, order, pageIndex, pageSize);
        }

        public IList<T> SelectListByPage<T>(string[] tables, string[] properties, string condition, OracleParameter[] conditionParameters, string order, int pageIndex, int pageSize) where T : new()
        {
            string sql = null;
            OracleParameter[] parameters = null;
            GenerateSelectListByPageSql<T>(tables, properties, condition, conditionParameters, order, pageIndex, pageSize, out sql, out parameters);
            OracleDataReaderExtension reader = _operation.ExecuteReaderExtension(sql, parameters);
            try
            {
                return GetEntitiesFromReader<T>(properties, reader.Reader);
            }
            finally
            {
                reader.Close();
            }
        }

        #endregion


        #region SelectCount

        private void GenerateSelectCountSql<T>(string[] tables, string condition, OracleParameter[] conditionParameters, out string sql, out OracleParameter[] parameters)
        {
            EntityDefinition entity = EntityDefinitionManager.Get<T>();
            StringBuilder sqlBd = new StringBuilder();
            sqlBd.Append("select count(*) from ");
            GenerateFromClause<T>(tables, sqlBd);
            parameters = null;
            if (!string.IsNullOrEmpty(condition))
            {
                sqlBd.Append(" where ");
                sqlBd.Append(condition);
                parameters = conditionParameters;
            }
            sql = sqlBd.ToString();
        }

        public int SelectCount<T>(string condition, OracleParameter[] conditionParameters)
        {
            return SelectCount<T>(null, condition, conditionParameters);
        }

        public int SelectCount<T>(string[] tables, string condition, OracleParameter[] conditionParameters)
        {
            string sql = null;
            OracleParameter[] parameters = null;
            GenerateSelectCountSql<T>(tables, condition, conditionParameters, out sql, out parameters);
            return Convert.ToInt32(_operation.ExecuteScalar(sql, parameters));
        }

        #endregion


    }
}
